﻿using Dapper;
using HrEFCore;
using HrEFCore.Models;
using HrIService;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.SqlClient;

namespace HrService
{
	public class SGService : ISGService
	{
		private readonly MyDbContext myDbContext;

		public SGService(MyDbContext myDbContext)
		{
			this.myDbContext = myDbContext;
		}

		//计算人数
		public int Amount(string SalaryStandardId)
		{
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				string sql = $"select count(1) from HF  where  SalaryStandardId ='{SalaryStandardId}'";
				int row = conn.Query<int>(sql).FirstOrDefault();
				return row;
			}
		}

		public Task<List<CFFK>> CFFKXL()
		{
			return myDbContext.CFFK.ToListAsync();
		}

		public Task<List<CFSK>> CFSKXL()
		{
			return myDbContext.CFSK.ToListAsync();
		}

		public Task<List<CFTK>> CFTKXL()
		{
			return myDbContext.CFTK.ToListAsync();
		}

		public List<HF> GetHF(string SalaryStandardId, string tiao)
		{
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				string sql = $"select * from HF where SalaryStandardId ='{SalaryStandardId}'and {tiao} ";
				return conn.Query<HF>(sql).ToList();
			}
		}
		//根据机构查询
		public List<HF> HFSelectAsync(string selectedValue)
		{
			//SqlConnection conn = new SqlConnection("Data Source=DESKTOP-UI9U8SK\\MSSQLSERVER1;Initial Catalog=HR_DB;Integrated Security=True;Encrypt=False;")
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				string sql = $"select * from HF where {selectedValue} ";
				return conn.Query<HF>(sql).ToList();
			}

		}
		//根据薪酬编号查
		public async Task<HF> HFSGById(string SalaryStandardId)
		{
			return await myDbContext.HF.FirstAsync(e => e.SalaryStandardId == SalaryStandardId);
		}
		//根据人力资源编号查
		public async Task<List<HF>> HFSGById2(string HumanId)
		{
			return await myDbContext.HF.Where(e => e.HumanId == HumanId).ToListAsync();
		}

		public async Task<SG> SGById(string SalaryGrantId)
		{
			return await myDbContext.SG.FirstAsync(e => e.SalaryGrantId == SalaryGrantId);
		}

		//分页查询
		public async Task<FenYe<SG>> SGFenYeSelect(FenYe<SG> fenYe)
		{
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				//@pageSize, @keyName, @tableName, @currentPage, @rows
				var parameters = new DynamicParameters();
				//FenYe<SS> fenYe
				parameters.Add("@currentPage", value: fenYe.PageIndex);
				parameters.Add("@pageSize", value: fenYe.PageSize);
				parameters.Add("@keyName", value: "SgrId");
				parameters.Add("@tableName", value: "SG");
				parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
				string sql = " exec [dbo].[proc_FenYe]  @currentPage, @pageSize, @keyName,  @tableName,  @rows OUTPUT";
				fenYe.List = await conn.QueryAsync<SG>(sql, parameters);
				fenYe.Rows = parameters.Get<int>("rows");
				return fenYe;
			}
		}

		public async Task<int> SGFHUpdate(SG sg)
		{
			var s = myDbContext.SG.Attach(sg);
			s.Property(e => e.SalaryPaidSum).IsModified = true;
			s.Property(e => e.Checker).IsModified = true;
			s.Property(e => e.CheckTime).IsModified = true;
			s.Property(e => e.CheckStatus).IsModified = true;
			return await myDbContext.SaveChangesAsync();
		}

		public async Task<int> SGInsert(SG sg)
		{
			//myDbContext .SG.AddAsync(sg);
			//return myDbContext.SaveChangesAsync();
			var s = myDbContext.SG.Attach(sg);
			s.Property(e => e.SalaryGrantId).IsModified = true;
			s.Property(e => e.SalaryStandardId).IsModified = true;
			s.Property(e => e.FirstKindId).IsModified = true;
			s.Property(e => e.FirstKindName).IsModified = true;
			s.Property(e => e.SecondKindId).IsModified = true;
			s.Property(e => e.SecondKindName).IsModified = true;
			s.Property(e => e.ThirdKindId).IsModified = true;
			s.Property(e => e.ThirdKindName).IsModified = true;
			s.Property(e => e.HumanAmount).IsModified = true;
			s.Property(e => e.SalaryStandardSum).IsModified = true;
			s.Property(e => e.SalaryPaidSum).IsModified = true;
			s.Property(e => e.Register).IsModified = true;
			s.Property(e => e.RegistTime).IsModified = true;
			s.Property(e => e.Checker).IsModified = true;
			s.Property(e => e.CheckTime).IsModified = true;
			s.Property(e => e.CheckStatus).IsModified = true;
			return await myDbContext.SaveChangesAsync();
		}

		//下拉列表
		public async Task<List<SG>> SGSelectXL()
		{
			List<SG> list;
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				string sql = "select * from view_2";
				list = (List<SG>)await conn.QueryAsync<SG>(sql);
				return list;
			}
		}

		//模糊查询
		public async Task<FenYe<SG>> SGWhere(int currentPage, int pageSize, string where)
		{
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				//@pageSize,@keyName,@tableName,@where,@currentPage,@lie ,@rows
				var parameters = new DynamicParameters();
				parameters.Add("pageSize", dbType: DbType.Int32, direction: ParameterDirection.Input, value: pageSize);
				parameters.Add("keyName", dbType: DbType.String, direction: ParameterDirection.Input, value: "SgrId");
				parameters.Add("tabelName", dbType: DbType.String, direction: ParameterDirection.Input, value: "SG");
				parameters.Add("where", dbType: DbType.String, direction: ParameterDirection.Input, value: where);
				parameters.Add("currentPage", dbType: DbType.Int32, direction: ParameterDirection.Input, value: currentPage);
				parameters.Add("lie", dbType: DbType.String, direction: ParameterDirection.Input, value: "*");
				parameters.Add("rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
				string sql = "[dbo].[proc_Fenye2]";
				IEnumerable<SG> list = await conn.QueryAsync<SG>(sql, parameters, commandType: CommandType.StoredProcedure);
				int rows = parameters.Get<int>("@rows");
				FenYe<SG> fenYe = new FenYe<SG>()
				{
					List = list,
					Rows = rows
				};
				return fenYe;
			}
		}

		public List<SSD> SSDById(string StandardId)
		{
			return myDbContext.SSD.Where(e => e.StandardId == StandardId).ToList();
		}
	}
}